﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Security.Cryptography;
using System.Windows.Forms;

namespace Lib.Update
{
    public class SQLDB : IDisposable
    {
        private string Decrypt(string cipherString, bool useHashing)
        {
            if (string.IsNullOrEmpty(cipherString)) return "";

            byte[] keyArray;
            //get the byte code of the string

            byte[] toEncryptArray = Convert.FromBase64String(cipherString);

            //System.Configuration.AppSettingsReader settingsReader =
            //                                    new AppSettingsReader();
            //Get your key from config file to open the lock!
            string key = "3.03.01.360";// (string)settingsReader.GetValue("SecurityKey",
            //typeof(String));

            if (useHashing)
            {
                //if hashing was used get the hash code with regards to your key
                MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
                //release any resource held by the MD5CryptoServiceProvider

                hashmd5.Clear();
            }
            else
            {
                //if hashing was not implemented get the byte code of the key
                keyArray = UTF8Encoding.UTF8.GetBytes(key);
            }

            TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
            //set the secret key for the tripleDES algorithm
            tdes.Key = keyArray;
            //mode of operation. there are other 4 modes. 
            //We choose ECB(Electronic code Book)

            tdes.Mode = CipherMode.ECB;
            //padding mode(if any extra byte added)
            tdes.Padding = PaddingMode.PKCS7;

            ICryptoTransform cTransform = tdes.CreateDecryptor();
            byte[] resultArray = cTransform.TransformFinalBlock(
                                 toEncryptArray, 0, toEncryptArray.Length);
            //Release resources held by TripleDes Encryptor                
            tdes.Clear();
            //return the Clear decrypted TEXT
            return UTF8Encoding.UTF8.GetString(resultArray);
        }

        private string GetConnectionString()
        {
            if (System.IO.File.Exists(Application.StartupPath + @"\Server.inf")) ;
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Application.StartupPath + @"\Server.inf");
                if (ds != null || ds.Tables.Count > 0)
                {
                    DataTable dtCfg = ds.Tables["DataConnection"];
                    if (dtCfg != null && dtCfg.Rows.Count > 0)
                    {
                        string ServerName = dtCfg.Rows[0]["ServerName"].ToString();
                        string DatabaseName = dtCfg.Rows[0]["DatabaseName"].ToString();
                        string UserName = dtCfg.Rows[0]["UserName"].ToString();
                        string Password = Decrypt(dtCfg.Rows[0]["Password"].ToString(), true);

                        return string.Format("User ID={0};pwd={1};data source={2};persist security info=False;initial catalog={3}",
                            UserName, Password, ServerName, DatabaseName);
                    }
                }
                return "";
            }
            return "";
        }

        string strConn = "";

        SqlConnection sqlConn;
        SqlCommand sqlCmd;
        SqlTransaction sqlTrans;

        public SQLDB()
        {
            strConn = GetConnectionString();

            sqlConn = new SqlConnection(strConn);
        }

        public void Dispose()
        {
            if (sqlTrans != null)
                sqlTrans.Dispose();
            if (sqlCmd != null)
                sqlCmd.Dispose();
            if (sqlConn != null)
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
                sqlConn.Dispose();
            }
        }

        public void BeginTrans()
        {
            if (sqlConn != null)
                sqlTrans = sqlConn.BeginTransaction();
            else
            {
                Open();
                sqlTrans = sqlConn.BeginTransaction();
            }
        }

        public void CommitTrans()
        {
            if (sqlTrans != null)
                sqlTrans.Commit();
        }

        public void RoolbackTrans()
        {
            if (sqlTrans != null)
                sqlTrans.Rollback();
        }

        private void Open()
        {
            if (sqlConn == null)
                sqlConn = new SqlConnection(strConn);
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();
        }

        private void SetTransaction()
        {
            if (!Object.Equals(sqlTrans, null))
                sqlCmd.Transaction = sqlTrans;
        } 

        public object ExecScalar(string sql)
        {
            Open();

            sqlCmd = new SqlCommand(sql, sqlConn);
            sqlCmd.CommandType = CommandType.Text;

            SetTransaction();

            return sqlCmd.ExecuteScalar();
        }

        public void ExecNonQuery(string sql)
        {
            Open();

            sqlCmd = new SqlCommand(sql, sqlConn);
            sqlCmd.CommandType = CommandType.Text;

            SetTransaction();

            sqlCmd.ExecuteNonQuery();
        }
    }
}
